2. Alter - update

Here’s a clear and direct difference between ALTER and UPDATE in SQL:

✅ ALTER TABLE

Purpose:

Used to change the structure (schema) of a table.

What it does:

Example:

-- Add a new column
ALTER TABLE students ADD email VARCHAR(100);

-- Change column data type
ALTER TABLE students ALTER COLUMN age TINYINT;

-- Drop a column
ALTER TABLE students DROP COLUMN marks;

Think of it as:

Schema-level changes

✅ UPDATE

Purpose:

Used to change data inside the table rows.

What it does:

Example:

-- Update a single row's marks
UPDATE students SET marks = 90 WHERE id = 1;

-- Increase all students' marks by 5
UPDATE students SET marks = marks + 5;

Think of it as:

Data-level changes

🔁 Summary Table:

Feature ALTER TABLE UPDATE
Changes Table structure/schema Data/records in rows
Common use Add/remove/modify columns, constraints Modify values of columns
Example ALTER TABLE students ADD age INT UPDATE students SET age = 20

Interview Tip:

If asked in an interview:

"ALTER modifies structure, UPDATE modifies data."

Let me know if you want examples of ALTER used with constraints or UPDATE with joins.

Here are some common tasks you can achieve using the ALTER command:

1. Rename a column in a table

ALTER TABLE Student RENAME COLUMN Column_NAME TO Other_new_name;

example Query
ALTER TABLE Student RENAME Column name TO FIRST_NAME;

2. Rename a table

ALTER TABLE Student RENAME TO Student_Details;

3. Add / Delete a column

ALTER TABLE table_name ADD column_name datatype;

example Query
ALTER TABLE Student ADD marks INT;

delete a column

ALTER TABLE table_name DROP COLUMN column_name;

4. Modify a Column Data Type

ALTER TABLE table_name  
MODIFY COLUMN column_name new_datatype;

example Query
ALTER TABLE Student_Details MODIFY COLUMN phone BIGINT;

Q. alter table column id and convert the column into primary key

alter table company add primary key(id)

here company is the table name and id is the column name

Q make the id column auto increment

here when doing autoincrement we need to specify the column datatype so here we again write the int

 alter table company
    -> modify id int not null  auto_increment;

so now to insert the data into table do
insert into company(name,salary) values("sanskar",50000); here specify the columns where the data is been added

Question

u have 2 table

student1
+-----------+-----------+------------+-------------+---------+
| studentid | firstname | lastname   | dateofbirth | classid |
+-----------+-----------+------------+-------------+---------+
|         1 | sanskar   | bhushankar | 2004-02-28  |      10 |
+-----------+-----------+------------+-------------+---------+

classes
+---------+-----------+-----------+
| classid | classname | teacherid |
+---------+-----------+-----------+
|       1 | math      |        23 |
+---------+-----------+-----------+

add a foreign key classidf to students table referencing classid in the classes table

query

mysql> alter table student1
    -> add constraint fadd
    -> foreign key(classidf) references classes(classid);
Query OK, 1 row affected (0.62 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from student1;
+-----------+-----------+------------+-------------+---------+-------+----------+
| studentid | firstname | lastname   | dateofbirth | classid | email | classidf |
+-----------+-----------+------------+-------------+---------+-------+----------+
|         1 | sanskar   | bhushankar | 2004-02-28  |      10 |       |     NULL |
+-----------+-----------+------------+-------------+---------+-------+----------+
1 row in set (0.00 sec)

The line ADD CONSTRAINT fadd is

🔧 Naming the Foreign Key Constraint

ALTER TABLE student1 DROP FOREIGN KEY fadd;

If you don’t give a name, MySQL generates one like student1_ibfk_1, which is hard to manage.
So, ADD CONSTRAINT fadd is just:

"Hey MySQL, I'm adding a foreign key, and I'm calling it fadd."

mysql> select * from orders;  
+---------+----------+---------+  
| orderid | quantity | status |  
+---------+----------+---------+  
| 1 | 2 | pending |  
+---------+----------+---------+  
1 row in set (0.08 sec)

mysql> insert into orders(orderid,quantity)values(2,6);  
Query OK, 1 row affected (0.04 sec)

mysql> insert into orders set status="done" where quantity=2;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where quantity=2' at line 1  
mysql>

The error is because INSERT doesn't support WHERE clauses — you're mixing up INSERT and UPDATE.

mysql> update orders set status="done" where quantity=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+---------+----------+---------+
| orderid | quantity | status  |
+---------+----------+---------+
|       1 |        2 | done    |
|       2 |        6 | pending |
+---------+----------+---------+
2 rows in set (0.00 sec)

Great — here's a list of interview-level SQL questions using the students table you created earlier:

CREATE TABLE students (
	id INT PRIMARY KEY,
	name VARCHAR(20),
	age INT,
	marks INT,
	dept VARCHAR(20)
);

🔶 ALTER TABLE — Schema Modification Based Questions

1. Add a new column for email

“Add an email column that stores student email addresses and can be null.”

ALTER TABLE students ADD email VARCHAR(100);

2. Change the data type of marks from INT to FLOAT

“Change the marks column to allow decimal values.”

ALTER TABLE students ALTER COLUMN marks FLOAT;

3. Add a default department

“Set a default department to ‘CSE’ for future inserts.”

ALTER TABLE students ADD CONSTRAINT df_dept DEFAULT 'CSE' FOR dept;

4. Drop the age column

“We no longer need age – drop it from the table.”

ALTER TABLE students DROP COLUMN age;

5. Rename the column name to full_name

“Rename the name column to full_name.” (In SQL Server)

EXEC sp_rename 'students.name', 'full_name', 'COLUMN';

🔶 UPDATE TABLE — Data Modification Based Questions

6. Increase marks by 10 for all students in the CSE department

“Give 10 bonus marks to all CSE students.”

UPDATE students SET marks = marks + 10 WHERE dept = 'CSE';

7. Set marks to NULL for students who have less than 35

“Failing students’ marks should be hidden.”

UPDATE students SET marks = NULL WHERE marks < 35;

8. Update department to ‘ECE’ where student name is ‘John’

“Correct the department for student John.”

UPDATE students SET dept = 'ECE' WHERE name = 'John';

9. Set default marks to 50 for students with NULL

“Assign 50 marks to students whose marks are not yet entered.”

UPDATE students SET marks = 50 WHERE marks IS NULL;

10. Swap departments for two groups of students

“Change CSE students to IT and IT to CSE.”
(This is tricky — must use CASE to prevent overlap.)

UPDATE students
SET dept = CASE 
             WHEN dept = 'CSE' THEN 'IT'
             WHEN dept = 'IT' THEN 'CSE'
             ELSE dept
           END;

⚠️ Tip for Interviews:

If they ask for data + structure changes, be ready to combine ALTER and UPDATE, like: